<?php
class Cms_Model_DAO_Course
{    
	private $_db;
	private static $_instance = null;
 
	/**
	 * @return Cms_Model_DAO_Course
	 * */
	public static function factory() {
		if(self::$_instance == null) {
			self::$_instance = new self();
		}
		
		return self::$_instance;
	}
	
    private function __construct()
    {
    	$module = Utils_Global::get('module');
		$this->_db = Utils_Global::getDbInstance($module);
    }
	
    /**
     * Get list of Courses by given options
     * @param array $options
     * @return array
     * */
	public function getCourses($options = array()) {
		$sql = "SELECT c.*, `t`.`lang`, `t`.`seo_title`, `t`.`content` ";
		$from = " FROM `course` c INNER JOIN `translation` t ON  `c`.`i18n_title` = `t`.`i18n_id` ";
		$where = " WHERE `c`.`active` = 1 ";
		if(is_array($options)) {
		    if($options['lang']) {
		        $where .= " AND `t`.`lang` = {$this->_db->quote($options['lang'])} ";
		        if($options['include_seo']) {
		            $where .= " AND `t2`.`lang` = {$this->_db->quote($options['lang'])} ";
		            $where .= " AND `t3`.`lang` = {$this->_db->quote($options['lang'])} ";
		            $where .= " AND `t4`.`lang` = {$this->_db->quote($options['lang'])} ";
		        }
		    }
		    if($options['id']) {
		        $where .= " AND `c`.`id` = {$this->_db->quote($options['id'], 'INTEGER')} ";
		    }
		    if($options['parent']) {
		        $where .= " AND `c`.`parent` = {$this->_db->quote($options['parent'], 'INTEGER')} ";
		    } else {
		        $where .= " AND `c`.`parent` = 0 ";
		    }
		    if($options['content']) {
		        $where .= " AND `t`.`content` LIKE '%" . $options['content'] . "%' ";
		    }
		}
		$order = " ORDER BY `c`.`order` ASC ";
		if($options['order'] && $options['by']) {
			$order = " ORDER BY `c`.`{$options['order']}` {$options['by']} ";
		}
		if($options['include_seo']) {
		    $sql .= ", `t2`.`content` `seo_title_content`, `t3`.`content` `seo_description_content`, `t4`.`content` `seo_keyword_content` ";
		    $from .= " INNER JOIN `translation` `t2` ON `c`.`i18n_seo_title` = `t2`.`i18n_id`
		                INNER JOIN `translation` `t3` ON `c`.`i18n_seo_description` = `t3`.`i18n_id`
		                INNER JOIN `translation` `t4` ON `c`.`i18n_seo_keywords` = `t4`.`i18n_id` ";
		}
		
		$limit = "";
		if(isset($options['offset']) && $options['offset'] >=0 && $options['limit'] > 0) {
			$limit = " LIMIT {$this->_db->quote($options['offset'], 'INTEGER')}, {$this->_db->quote($options['limit'], 'INTEGER')} "; 
		}
		$result = $this->_db->fetchAll ( $sql . $from . $where . $order . $limit );
		return $result;
	}
	
	public function getAllCourses($options = array()) {
	    $sql = "SELECT `c`.*, `t`.`lang`, `t`.`seo_title`, `t`.`content` ";
	    $from = " FROM `course` c INNER JOIN `translation` t ON  `c`.`i18n_title` = `t`.`i18n_id` ";
	    $where = " WHERE `c`.`active` = 1 ";
	    if(is_array($options)) {
	    	if($options['lang']) {
	    		$where .= " AND `t`.`lang` = {$this->_db->quote($options['lang'])} ";
	    	}
	    	if($options['id']) {
	    		$where .= " AND `c`.`id` = {$this->_db->quote($options['id'], 'INTEGER')} ";
	    	}
	    }
	    $order = " ORDER BY `c`.`order` ASC ";
	    if($options['order'] && $options['by']) {
	    	$order = " ORDER BY `c`.`{$options['order']}` {$options['by']} ";
	    }
	    
	    $limit = "";
	    if(isset($options['offset']) && $options['offset'] >=0 && $options['limit'] > 0) {
	    	$limit = " LIMIT {$this->_db->quote($options['offset'], 'INTEGER')}, {$this->_db->quote($options['limit'], 'INTEGER')} ";
	    }
	    $result = $this->_db->fetchAll ( $sql . $from  . $where . $order . $limit );
	    return $result;
	}
	
	public function getTabs($options = array()) {
	    $sql = "SELECT `ta`.`id`, `t`.`content` `title`, `t2`.`content` `content` ";
	    $from = " FROM `tabs` ta INNER JOIN `translation` t ON `ta`.`i18n_title` = `t`.`i18n_id` 
	                             INNER JOIN `translation` t2 ON `ta`.`i18n_content` = `t2`.`i18n_id` ";
	    $where = " WHERE `ta`.`course_id` = {$this->_db->quote($options['id'], 'INTEGER')} 
	                        AND `t`.`lang` = {$this->_db->quote($options['lang'])} AND `t2`.`lang` = {$this->_db->quote($options['lang'])} ";
	    $order = " ORDER BY `ta`.`order` ASC ";
	    
	    $result = $this->_db->fetchAll($sql . $from . $where . $order);
	    return $result;
	}
	
	/**
	 * Get course count by given condition
	 * @param array $options
	 * @return integer
	 * */
	public function getCourseCount($options=array()) {
		$sql = "SELECT count(*) as `count` ";
		$from = " FROM `course` c INNER JOIN `translation` t ON  `c`.`i18n_title` = `t`.`i18n_id`  ";
		$where = " WHERE active = 1 ";
	    if(is_array($options)) {
		    if($options['lang']) {
		        $where .= " AND `t`.`lang` = {$this->_db->quote($options['lang'])} ";
		    }
		    if($options['id']) {
		        $where .= " AND `c`.`id` = {$this->_db->quote($options['id'], 'INTEGER')} ";
		    }
		    if($options['parent']) {
		        $where .= " AND `c`.`parent` = {$this->_db->quote($options['parent'], 'INTEGER')} ";
		    } else {
		        $where .= " AND `c`.`parent` = 0 ";
		    }
		    if($options['content']) {
		        $where .= " AND `t`.`content` LIKE '%" . $options['content'] . "%' ";
		    }
		}
		
		$result = $this->_db->fetchOne($sql . $from . $where);
		return $result;
	}
}